package edu.wata.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import org.apache.log4j.Logger;

import com.wata.db.utils.DBConnection;
import com.wata.db.utils.DBUtils;
import com.wata.db.utils.JdbcTemplate;

import edu.wata.dao.iface.UserDao;
import edu.wata.domain.Page;
import edu.wata.domain.UMQueryKeyword;
import edu.wata.domain.User;
import edu.wata.domain.UserDetail;

public class UserJdbcDaoImpl implements UserDao {

	private static Logger log = Logger.getLogger(UserJdbcDaoImpl.class);

	@Override
	public int checkUser(String name, String password) {
		int rowNum = 0;
		String sql = "select count(1) from users where name=? and password=?";
		rowNum = JdbcTemplate.queryForCount(sql, name, password);
		return rowNum;
	}

	Integer i;

	@Override
	public int insertUser(User user) {
		int userId = -1;
		String sql = "insert into users values(users_id.nextval,?,?,?,?)";
		Connection connection = DBConnection.getConnection();
		try {
			PreparedStatement psmt = connection.prepareStatement(sql, new String[] { "id" });
			psmt.setString(1, user.getName());
			psmt.setString(2, user.getPassword());
			psmt.setString(3, user.getSex());
			psmt.setString(4, user.getEmail());
			DBUtils.printRealSql(sql,
					new Object[] { user.getName(), user.getPassword(), user.getSex(), user.getEmail() });
			int rowNum = psmt.executeUpdate();
			ResultSet rs = psmt.getGeneratedKeys();
			if (rowNum == 1 && rs.next()) {
				userId = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return userId;
	}

	@Override
	public int insertUserDetail(UserDetail ud) {
		int rowNum = 0;
		String sql = "insert into userDetail values(userdetail_id.nextval,?,?,?)";
		rowNum = JdbcTemplate.update(sql, ud.getNativePlace_code(), ud.getHobby_code(), ud.getUserId());
		return rowNum;
	}

	@Override
	public List<Map<String, Object>> queryAll() {
		String sql = "select "
				+ "u.id,u.name,u.password,u.sex,u.email,np.code nativePlace_code,np.name nativePlace_name,ud.hobby_code "
				+ "from users u left outer join Userdetail ud on u.id=ud.userid "
				+ "left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id";
		List<Map<String, Object>> list = JdbcTemplate.query(sql, (rs) -> {
			List<Map<String, Object>> list0 = new ArrayList<>();
			try {
				int count = 0;
				while (rs.next()) {
					count++;
					Map<String, Object> map = new HashMap<>();
					for (int i = 1, len = rs.getMetaData().getColumnCount(); i <= len; i++) {
						map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
					}
					list0.add(map);
				}
			} catch (SQLException e) {
				log.debug("queryall method error,message is " + e.getMessage());
			}
			return list0;
		}, null);
		return list;
	}

	@Override
	public int deleteUsers(String ids) {
		int rowNum = 0;
		String sql = "delete from users where id in (" + ids + ")";
		rowNum = JdbcTemplate.update(sql, new Object[] {});
		return rowNum;
	}

	@Override
	public int deleteUserDetail(String ids) {
		int rowNum = 0;
		String sql = "delete from userDetail where userid in (" + ids + ")";
		rowNum = JdbcTemplate.update(sql, new Object[] {});
		return rowNum;
	}

	@Override
	public Map<String, Object> queryUserById(String id) {
		String sql = "select "
				+ "u.id,u.name,u.password,u.sex,u.email,np.code nativePlace_code,np.name nativePlace_name,ud.hobby_code "
				+ "from users u left outer join Userdetail ud on u.id=ud.userid "
				+ "left outer join nativeplace np on ud.nativeplace_code=np.code where u.id=? order by u.id";
		Map<String, Object> map = JdbcTemplate.query(sql, (ResultSet rs) -> {
			Map<String, Object> map0 = new HashMap<>();
			try {
				if (rs.next()) {
					for (int i = 1, len = rs.getMetaData().getColumnCount(); i <= len; i++) {
						map0.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
					}
				}
			} catch (SQLException e) {
				log.debug("queryUserById method error,message is " + e.getMessage());
			}
			return map0;
		}, new Object[] { id });
		return map;
	}

	@Override
	public int updateUser(User user) {
		int rowNum = 0;
		String sql = "update users set name=?,password=?,sex=?,email=? where id=?";
		rowNum = JdbcTemplate.update(sql, user.getName(), user.getPassword(), user.getSex(), user.getEmail(),
				user.getId());
		return rowNum;
	}

	@Override
	public int updateUserDetail(UserDetail userDetail) {
		int rowNum = 0;
		String sql = "update userDetail set nativeplace_code=?,hobby_code=? where userId=?";
		rowNum = JdbcTemplate.update(sql, userDetail.getNativePlace_code(), userDetail.getHobby_code(),
				userDetail.getUserId());
		return rowNum;
	}

	@Override
	public void queryByPage(Page<List<Map<String, Object>>> page) {
		String sql = "select * from (" + "  select baseTable.*,rownum as rn from ("
				+ "    select u.id,u.name,u.password,u.sex,u.email,np.name nativePlace_name,ud.hobby_code "
				+ "           from users u left outer join Userdetail ud on u.id=ud.userid "
				+ "           left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id) baseTable "
				+ "  where rownum<=(?)*?) " + "where rn>(?-1)*?";
		List<Map<String, Object>> list = JdbcTemplate.query(sql, (rs) -> {
			List<Map<String, Object>> list0 = new ArrayList<>();
			try {
				while (rs.next()) {
					Map<String, Object> map = new HashMap<>();
					for (int i = 1, len = rs.getMetaData().getColumnCount(); i <= len; i++) {
						map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
					}
					list0.add(map);
				}
			} catch (SQLException e) {
				log.debug("query by page method error,message is " + e.getMessage());
			}
			return list0;
		}, new Object[] { page.getCurrentPage(), page.getRowNumber(), page.getCurrentPage(), page.getRowNumber(), });
		page.setPageData(list);
	}

	@Override
	public void getTotalRow(Page<List<Map<String, Object>>> page) {
		String sql = "select count(1) from users u left outer join Userdetail ud on u.id=ud.userid"
				+ " left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id";
		int totalRow = JdbcTemplate.queryForCount(sql, new Object[] {});

		page.setTotalRow(totalRow);
	}

	@Override
	public void queryByPage(Page<List<Map<String, Object>>> page, UMQueryKeyword qk) {
		List<Object> params = new ArrayList<>();
		if (!Objects.equals("", qk.getName())) {
			params.add("%" + qk.getName() + "%");
		}
		if (!Objects.equals("", qk.getSex())) {
			params.add(qk.getSex());
		}
		if (!Objects.equals("", qk.getHobbyCode())) {
			params.add("%" + qk.getHobbyCode() + "%");
		}
		params.add(page.getCurrentPage());
		params.add(page.getRowNumber());
		params.add(page.getCurrentPage());
		params.add(page.getRowNumber());
		String beginSQL = "select * from ( select baseTable.*,rownum as rn from (";
		String SQL = "select u.id,u.name,u.password,u.sex,u.email,np.name nativePlace_name,ud.hobby_code "
				+ "from users u left outer join Userdetail ud on u.id=ud.userid "
				+ "left outer join nativeplace np on ud.nativeplace_code=np.code where 1=1 ";
		if (!Objects.equals("", qk.getName())) {
			SQL += "and u.name like ? ";
		}
		if (!Objects.equals("", qk.getSex())) {
			SQL += "and u.sex=? ";
		}
		if (!Objects.equals("", qk.getHobbyCode())) {
			SQL += "and ud.hobby_code like ?";
		}
		String endSQL = " order by u.id) baseTable where rownum<=(?)*?) where rn>(?-1)*?";
		List<Map<String, Object>> list = JdbcTemplate.query(beginSQL + SQL + endSQL, (rs) -> {
			List<Map<String, Object>> list0 = new ArrayList<>();
			try {
				while (rs.next()) {
					Map<String, Object> map = new HashMap<>();
					for (int i = 1, len = rs.getMetaData().getColumnCount(); i <= len; i++) {
						map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
					}
					list0.add(map);
				}
			} catch (SQLException e) {
				log.debug("query by page method error,message is " + e.getMessage());
			}
			return list0;
		}, params.toArray());
		page.setPageData(list);

	}

	@Override
	public void getTotalRow(Page<List<Map<String, Object>>> page, UMQueryKeyword qk) {
		List<Object> params = new ArrayList<>();
		if (!Objects.equals("", qk.getName())) {
			params.add("%" + qk.getName() + "%");
		}
		if (!Objects.equals("", qk.getSex())) {
			params.add(qk.getSex());
		}
		if (!Objects.equals("", qk.getHobbyCode())) {
			params.add("%" + qk.getHobbyCode() + "%");
		}
		String sql = "select count(1) from users u left outer join Userdetail ud on u.id=ud.userid"
				+ " left outer join nativeplace np on ud.nativeplace_code=np.code where 1=1 ";
		if (!Objects.equals("", qk.getName())) {
			sql += "and u.name like ? ";
		}
		if (!Objects.equals("", qk.getSex())) {
			sql += "and u.sex=? ";
		}
		if (!Objects.equals("", qk.getHobbyCode())) {
			sql += "and ud.hobby_code like ?";
		}
		sql += " order by u.id";
		int totalRow = JdbcTemplate.queryForCount(sql, params.toArray());
		page.setTotalRow(totalRow);

	}

}
